/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yy.tool;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.URL;
import java.net.URLConnection;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Locale;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

// <editor-fold defaultstate="collapsed" desc="class for set style">

/**
 *
 * @author Administrator
 */
public class ExcelTool {

    public static CellStyleDefined titleStyle;
    public static CellStyleDefined commonStyle;
    public static CellStyleDefined commonStyle_mul;

    public static Object getValue(Sheet sheet, int row, int col) {
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            return "";
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            return "";
        }
        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cellType == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cellType == Cell.CELL_TYPE_ERROR) {
            return cell.getErrorCellValue();
        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                String srcDate = cell.getDateCellValue() == null ? "" : cell.getDateCellValue().toString();
                SimpleDateFormat srcFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
                SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd");
                if (srcDate.equals("")) {
                    return null;
                }
                String targetDate = "";
                try {
                    targetDate = targetFormat.format(srcFormat.parse(srcDate));
                } catch (ParseException ex) {
                    ex.printStackTrace();
                }
                return targetDate;
            } else {
                return cell.getNumericCellValue();
            }
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else {
            return "";
        }
    }

    public static void setValue(Sheet sheet, int row, int col, String value) {
        if (value != null && value.endsWith(".0")) {
            value = value.substring(0, value.length() - 2);
        }
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        cell.setCellValue(value);
    }

    public static void setValueAndHeight(Sheet sheet, int row, int col, String value) {
        if (value != null && value.endsWith(".0")) {
            value = value.substring(0, value.length() - 2);
        }
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        rowCell.setHeightInPoints((short) 20);
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        cell.setCellValue(value);
    }

    public static void setStyle(Sheet sheet, int row, int col, Workbook workbook) {
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        CellStyle sourceStyle = cell.getCellStyle();
        CellStyle newStyle = workbook.createCellStyle();
        newStyle.setAlignment(sourceStyle.getAlignment());
        newStyle.setBorderBottom(sourceStyle.getBorderBottom());
        newStyle.setBorderLeft(sourceStyle.getBorderLeft());
        newStyle.setBorderRight(sourceStyle.getBorderRight());
        newStyle.setBorderTop(sourceStyle.getBorderTop());
        newStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        newStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
        cell.setCellStyle(newStyle);
    }

    public static String getConvertedValue(Sheet sheet, int i, int j, String clolumnName) {
        String value = "";
        Object o = ExcelTool.getValue(sheet, i, j);

        if (o != null && !o.toString().trim().equals("")) {
            value = o.toString();
        } else {
            value = "";
        }
        value = convertNAToNULL(value);
        if (value != null) {
            value = value.replaceAll("\r", " ");
            value = value.replaceAll("\n", " ");
            value = value.replaceAll("\\\\", " ");
            if (clolumnName != null && !clolumnName.startsWith("CMNT_TXT")) {
                value = value.replaceAll("'", " ");
            }
        }
        if (clolumnName == null || !clolumnName.startsWith("CMNT_TXT")) {
            if (value != null) {
                try {
                    value = value.toUpperCase();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        if (value != null && value.endsWith(".0")) {
            value = value.substring(0, value.length() - 2);
        }
        return value;
    }

    public static String convertNAToNULL(String value) {
        if (value != null) {
            if (value.equalsIgnoreCase("na") || value.equals("")) {
                value = "";
            }
        }
        return value;
    }

    public static String converentCol(String col) {
        if (col.equalsIgnoreCase("A")) {
            col = "0";
        } else if (col.equalsIgnoreCase("B")) {
            col = "1";
        } else if (col.equalsIgnoreCase("C")) {
            col = "2";
        } else if (col.equalsIgnoreCase("D")) {
            col = "3";
        } else if (col.equalsIgnoreCase("E")) {
            col = "4";
        } else if (col.equalsIgnoreCase("F")) {
            col = "5";
        } else if (col.equalsIgnoreCase("G")) {
            col = "6";
        } else if (col.equalsIgnoreCase("H")) {
            col = "7";
        } else if (col.equalsIgnoreCase("I")) {
            col = "8";
        } else if (col.equalsIgnoreCase("J")) {
            col = "9";
        } else if (col.equalsIgnoreCase("K")) {
            col = "10";
        } else if (col.equalsIgnoreCase("L")) {
            col = "11";
        } else if (col.equalsIgnoreCase("M")) {
            col = "12";
        } else if (col.equalsIgnoreCase("N")) {
            col = "13";
        } else if (col.equalsIgnoreCase("O")) {
            col = "14";
        } else if (col.equalsIgnoreCase("P")) {
            col = "15";
        } else if (col.equalsIgnoreCase("Q")) {
            col = "16";
        } else if (col.equalsIgnoreCase("R")) {
            col = "17";
        } else if (col.equalsIgnoreCase("S")) {
            col = "18";
        } else if (col.equalsIgnoreCase("T")) {
            col = "19";
        } else if (col.equalsIgnoreCase("U")) {
            col = "20";
        } else if (col.equalsIgnoreCase("V")) {
            col = "21";
        } else if (col.equalsIgnoreCase("W")) {
            col = "22";
        } else if (col.equalsIgnoreCase("X")) {
            col = "23";
        } else if (col.equalsIgnoreCase("Y")) {
            col = "24";
        } else if (col.equalsIgnoreCase("Z")) {
            col = "25";
        } else if (col.equalsIgnoreCase("AA")) {
            col = "26";
        } else if (col.equalsIgnoreCase("AB")) {
            col = "27";
        } else if (col.equalsIgnoreCase("AC")) {
            col = "28";
        } else if (col.equalsIgnoreCase("AD")) {
            col = "29";
        } else if (col.equalsIgnoreCase("AE")) {
            col = "30";
        } else if (col.equalsIgnoreCase("AF")) {
            col = "31";
        } else if (col.equalsIgnoreCase("AG")) {
            col = "32";
        } else if (col.equalsIgnoreCase("AH")) {
            col = "33";
        } else if (col.equalsIgnoreCase("AI")) {
            col = "34";
        } else if (col.equalsIgnoreCase("AJ")) {
            col = "35";
        } else if (col.equalsIgnoreCase("AK")) {
            col = "36";
        } else if (col.equalsIgnoreCase("AL")) {
            col = "37";
        } else if (col.equalsIgnoreCase("AM")) {
            col = "38";
        } else if (col.equalsIgnoreCase("AN")) {
            col = "39";
        } else if (col.equalsIgnoreCase("AO")) {
            col = "40";
        } else if (col.equalsIgnoreCase("AP")) {
            col = "41";
        } else if (col.equalsIgnoreCase("AQ")) {
            col = "42";
        } else if (col.equalsIgnoreCase("AR")) {
            col = "43";
        } else if (col.equalsIgnoreCase("AS")) {
            col = "44";
        } else if (col.equalsIgnoreCase("AT")) {
            col = "45";
        } else if (col.equalsIgnoreCase("AU")) {
            col = "46";
        } else if (col.equalsIgnoreCase("AV")) {
            col = "47";
        } else if (col.equalsIgnoreCase("AW")) {
            col = "48";
        } else if (col.equalsIgnoreCase("AX")) {
            col = "49";
        } else if (col.equalsIgnoreCase("AY")) {
            col = "50";
        } else if (col.equalsIgnoreCase("AZ")) {
            col = "51";
        } else if (col.equalsIgnoreCase("BA")) {
            col = "52";
        } else if (col.equalsIgnoreCase("BB")) {
            col = "53";
        } else if (col.equalsIgnoreCase("BC")) {
            col = "54";
        } else if (col.equalsIgnoreCase("BD")) {
            col = "55";
        } else if (col.equalsIgnoreCase("BE")) {
            col = "56";
        } else if (col.equalsIgnoreCase("BF")) {
            col = "57";
        } else if (col.equalsIgnoreCase("BG")) {
            col = "58";
        } else if (col.equalsIgnoreCase("BH")) {
            col = "59";
        } else if (col.equalsIgnoreCase("BI")) {
            col = "60";
        } else if (col.equalsIgnoreCase("BJ")) {
            col = "61";
        } else if (col.equalsIgnoreCase("BK")) {
            col = "62";
        } else if (col.equalsIgnoreCase("BL")) {
            col = "63";
        } else if (col.equalsIgnoreCase("BM")) {
            col = "64";
        } else if (col.equalsIgnoreCase("BN")) {
            col = "65";
        } else if (col.equalsIgnoreCase("BO")) {
            col = "66";
        } else if (col.equalsIgnoreCase("BP")) {
            col = "67";
        } else if (col.equalsIgnoreCase("BQ")) {
            col = "68";
        } else if (col.equalsIgnoreCase("BR")) {
            col = "69";
        } else if (col.equalsIgnoreCase("BS")) {
            col = "70";
        } else if (col.equalsIgnoreCase("BT")) {
            col = "71";
        } else if (col.equalsIgnoreCase("BU")) {
            col = "72";
        } else if (col.equalsIgnoreCase("BV")) {
            col = "73";
        } else if (col.equalsIgnoreCase("BW")) {
            col = "74";
        } else if (col.equalsIgnoreCase("BX")) {
            col = "75";
        } else if (col.equalsIgnoreCase("BY")) {
            col = "76";
        } else if (col.equalsIgnoreCase("BZ")) {
            col = "77";
        } else if (col.equalsIgnoreCase("CA")) {
            col = "78";
        } else if (col.equalsIgnoreCase("CB")) {
            col = "79";
        } else if (col.equalsIgnoreCase("CC")) {
            col = "80";
        } else if (col.equalsIgnoreCase("CD")) {
            col = "81";
        } else if (col.equalsIgnoreCase("CE")) {
            col = "82";
        } else if (col.equalsIgnoreCase("CF")) {
            col = "83";
        } else if (col.equalsIgnoreCase("CG")) {
            col = "84";
        } else if (col.equalsIgnoreCase("CH")) {
            col = "85";
        } else if (col.equalsIgnoreCase("CI")) {
            col = "86";
        } else if (col.equalsIgnoreCase("CJ")) {
            col = "87";
        } else if (col.equalsIgnoreCase("CK")) {
            col = "88";
        } else if (col.equalsIgnoreCase("CL")) {
            col = "89";
        } else if (col.equalsIgnoreCase("CM")) {
            col = "90";
        } else if (col.equalsIgnoreCase("CN")) {
            col = "91";
        } else if (col.equalsIgnoreCase("CO")) {
            col = "92";
        } else if (col.equalsIgnoreCase("CP")) {
            col = "93";
        } else if (col.equalsIgnoreCase("CQ")) {
            col = "94";
        } else if (col.equalsIgnoreCase("CR")) {
            col = "95";
        } else if (col.equalsIgnoreCase("CS")) {
            col = "96";
        } else if (col.equalsIgnoreCase("CT")) {
            col = "97";
        } else if (col.equalsIgnoreCase("CU")) {
            col = "98";
        } else if (col.equalsIgnoreCase("CV")) {
            col = "99";
        } else if (col.equalsIgnoreCase("CW")) {
            col = "100";
        } else if (col.equalsIgnoreCase("CX")) {
            col = "101";
        } else if (col.equalsIgnoreCase("CY")) {
            col = "102";
        } else if (col.equalsIgnoreCase("CZ")) {
            col = "103";
        }
        return col;
    }

    public static void setValue(Sheet sheet, int row, int col, String value, CellStyle style) {
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    public static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String value, CellStyle newStyle) {
        for (int i = firstRow; i <= lastRow; i++) {
            setHeight(sheet, i, 300);
            for (int j = firstCol; j <= lastCol; j++) {
                setWidth(sheet, j, 7000);
                setStyle(sheet, i, j, newStyle);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        setValue(sheet, firstRow, firstCol, value);
        Row rowCell = sheet.getRow(firstRow);
        if (rowCell == null) {
            rowCell = sheet.createRow(firstRow);
        }
        Cell cell = rowCell.getCell(firstCol);
        if (cell == null) {
            cell = rowCell.createCell(firstCol);
        }
        CellStyle style = cell.getCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cell.setCellStyle(style);
    }

    public static void setStyle(Sheet sheet, int row, int col, CellStyle style) {
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        cell.setCellStyle(style);
    }

    public static CellStyle getStyle(HSSFWorkbook workbook, CellStyleDefined Style) {
        CellStyle newStyle = workbook.createCellStyle();
        newStyle.setAlignment(Style.getAlignment());
        newStyle.setBorderBottom(Style.getBorderBottom());
        newStyle.setBorderLeft(Style.getBorderLeft());
        newStyle.setBorderRight(Style.getBorderRight());
        newStyle.setBorderTop(Style.getBorderTop());
        newStyle.setFillPattern(Style.getFillPattern());
        newStyle.setFillForegroundColor(Style.getFillForegroundColor());
        newStyle.setFont(Style.getFont());
        return newStyle;
    }

    public static void initPreDefinedStyle(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName(HSSFFont.FONT_ARIAL);
        font.setColor(HSSFColor.BLACK.index);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleStyle = new CellStyleDefined(HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.SOLID_FOREGROUND, HSSFColor.LIGHT_YELLOW.index, font);
        Font font1 = workbook.createFont();
        font1.setFontHeightInPoints((short) 10);
        font1.setFontName(HSSFFont.FONT_ARIAL);
        font1.setColor(HSSFColor.BLACK.index);
        commonStyle = new CellStyleDefined(HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.SOLID_FOREGROUND, HSSFColor.RED.index, font1);
        Font font2 = workbook.createFont();
        font2.setFontHeightInPoints((short) 10);
        font2.setFontName(HSSFFont.FONT_ARIAL);
        font2.setColor(HSSFColor.BLACK.index);
        commonStyle_mul = new CellStyleDefined(HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.SOLID_FOREGROUND, HSSFColor.LIGHT_YELLOW.index, font2);


    }

    public static void setWidth(Sheet sheet, int col, int width) {
        sheet.setColumnWidth(col, (short) width);
    }

    public static void setHeight(Sheet sheet, int row, int Height) {
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        rowCell.setHeight((short) Height);
    }

    public static Sheet getSheet(Workbook workbook, int index) {
        return workbook.getSheetAt(index);
    }

    public static Sheet getSheet(Workbook workbook, String name) {
        return workbook.getSheet(name);
    }

    public static void cloneSheet(Workbook workbook, int index) {
        workbook.cloneSheet(index);
    }

    public static String NullToEmpty(String dd) {
        if (dd == null) {
            return "";
        } else {
            return dd.trim();
        }
    }

    public static void writeDate(HSSFWorkbook workbook) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        java.util.Date date = new java.util.Date();
        String nowTime = dateFormat.format(date);
        HSSFSheet sheet = workbook.getSheet("Version");
        ExcelTool.setValue(sheet, 8, 8, nowTime);
    }

    public static Object getCellValue(Sheet sheet, int row, int col) {
        String string = "";
        Row rowCell = sheet.getRow(row);
        if (rowCell == null) {
            rowCell = sheet.createRow(row);
        }
        Cell cell = rowCell.getCell(col);
        if (cell == null) {
            cell = rowCell.createCell(col);
        }
        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue();
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        }
        return string;
    }

    public static void DeleteRegion(Sheet sheet) {
        int n = sheet.getNumMergedRegions();
        for (int i = n; i >= 0; i--) {
            sheet.removeMergedRegion(i);
        }
    }

    public static void DeleteCol(Sheet sheet, int col) {
        Iterator it = sheet.rowIterator();
        while (it.hasNext()) {
            Row row = (Row) it.next();
            int last = row.getLastCellNum();
            for (int i = col; i <= last; i++) {
                Cell cell = row.getCell(i);
                sheet.removeMergedRegion(i);
                if (cell != null) {
                    row.removeCell(cell);
                }
            }
        }
    }

    public static void AddRegion(Sheet sheet, String type) {
        if (type.equalsIgnoreCase("Field")) {
            for (int i = 0; i < 3; i++) {
                sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
            }
        } else {
            for (int i = 0; i < 6; i++) {
                sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
            }
        }
    }

    public static void downloadExcelTemplate(String server, String URL, String uri) {
        try {
            String templatePath = "";
            boolean isNew = true;
            String[] fileList = {"Export Template Metric.xls", "Export Template.xls", "Export Multi Template.xls", "Export Multi Template Metric.xls", "Export Internal Template Metric.xls", "Export Internal Template.xls",
                "RollupSummary-Imperial.xls", "RollupSummary-Metric.xls"};
            for (int i = 0; i < fileList.length; i++) {
                String string = fileList[i];
                templatePath = uri + string;
                isNew = ExcelTool.isNewTemplate(server, string, templatePath, "ExcelExport");
                if (!isNew) {
                    break;
                }
            }
            if (isNew) {
                return;
            }

            URL url = new URL(URL);
            URLConnection conn = url.openConnection();
            ObjectInputStream ois = new ObjectInputStream(conn.getInputStream());
            LinkedHashMap<String, byte[]> map = (LinkedHashMap<String, byte[]>) ois.readObject();
            ois.close();

            String filePath = uri;

            Set<String> keys = map.keySet();
            for (String key : keys) {
                filePath += key;
                FileOutputStream fos = new FileOutputStream(filePath);
                fos.write(map.get(key));
                fos.flush();
                fos.close();
                filePath = uri;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static boolean isNewTemplate(String server, String templateName, String filePath, String folder) {
        File file = new File(filePath);
        if (file.exists()) {
            long lastModified = file.lastModified();
            try {
                URL url = new URL(server + "/DownLoadExcelTemplateServlet?type=checkVersion&folder=" + folder);
                URLConnection conn = url.openConnection();
                conn.setUseCaches(false);
                conn.setDoInput(true);
                conn.setDoOutput(true);
                conn.setRequestProperty("Content-Type", "application/octet-stream");
                ObjectOutputStream oos = new ObjectOutputStream(conn.getOutputStream());
                oos.writeUTF(templateName);
                oos.flush();
                ObjectInputStream ois = new ObjectInputStream(conn.getInputStream());
                long servFileDate = ois.readLong();
                Date fileDate = new Date(lastModified);
                Date serverDate = new Date(servFileDate);
                ois.close();
                oos.close();
                return fileDate.after(serverDate);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return false;
    }

    public static boolean downTemplate(String server, String templateName, String filePath, String folder) {

        try {
            URL url = new URL(server + "/DownLoadExcelTemplateServlet?type=download&folder=" + folder);
            URLConnection conn = url.openConnection();
            conn.setUseCaches(false);
            conn.setDoInput(true);
            conn.setDoOutput(true);
            conn.setRequestProperty("Content-Type", "application/octet-stream");
            ObjectOutputStream oos = new ObjectOutputStream(conn.getOutputStream());
            oos.writeUTF(templateName);
            oos.flush();
            ObjectInputStream ois = new ObjectInputStream(conn.getInputStream());
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            byte[] b = new byte[1024 * 512];
            int lenght = 0;
            while ((lenght = ois.read(b)) > 0) {
                baos.write(b, 0, lenght);
            }
            File file = new File(filePath);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            FileOutputStream fos = new FileOutputStream(file);
            fos.write(baos.toByteArray());
            fos.flush();
            fos.close();
            ois.close();
            oos.close();
            baos.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

    // <editor-fold defaultstate="collapsed" desc="source coding from web">
    public static void rowSpan(HSSFSheet sheet, short column, int startRow, int toRow) {
        int fromIndex = 0, toIndex = 0;
        if (toRow > sheet.getLastRowNum()) {
            toRow = sheet.getLastRowNum();
        }
        String v = "";
        for (int i = startRow; i <= toRow; i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = row != null ? row.getCell(column) : null;
            if (cell != null && (cell.getCellType() != HSSFCell.CELL_TYPE_STRING || !cell.getStringCellValue().equals(v))) {
                if (toIndex > fromIndex) {
                    Region region = new Region(fromIndex, column, toIndex, column);
                    sheet.addMergedRegion(region);
                }
                fromIndex = i;
                v = cell.getStringCellValue();
            } else {
                toIndex = i;
            }
        }
        if (toIndex > fromIndex) {
            Region region = new Region(fromIndex, column, toIndex, column);
            sheet.addMergedRegion(region);
        }
    }

    public static void clearSheetBody(HSSFSheet sheet, int fromrow) {
        // 清除模板中的数据
        for (int i = fromrow; i <= sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i) != null) {
                sheet.removeRow(sheet.getRow(i));
            }
        }
    }

    public static void clearSheetBody(HSSFSheet sheet, int fromRow, int toRow) {
        // 清除模板中的数据
        for (int i = fromRow; i <= toRow; i++) {
            if (sheet.getRow(i) != null) {
                sheet.removeRow(sheet.getRow(i));
            }
        }
    }

    // 清除所有的区域
    public static void clearAllRegion(HSSFSheet sheet) {
        int n = sheet.getNumMergedRegions();
        for (int i = n; i >= 0; i--) {
            sheet.removeMergedRegion(i);
        }
    }

    /**
     *
     * @param sheet 区域所在的sheet
     * @param rownum 指定单元格所在行
     * @param column 指定单元格所在列 删除指定单元格所在的区域
     */
    public static void clearRegionAssign(HSSFSheet sheet, int rownum, int column) {
        int n = sheet.getNumMergedRegions();
        for (int i = n; i >= 0; i--) {
            Region region = sheet.getMergedRegionAt(i);
            if (region.getRowFrom() <= rownum && region.getRowTo() >= rownum && region.getColumnFrom() <= column && region.getColumnTo() >= column) {
                sheet.removeMergedRegion(i);
                break;
            }
        }
    }
    // </editor-fold>
}
class CellStyleDefined {

    short Alignment;
    short BorderTop;
    short BorderLeft;
    short BorderBottom;
    short BorderRight;
    short FillPattern;
    short FillForegroundColor;
    Font font;

    public CellStyleDefined(short Alignment, short BorderTop, short BorderLeft, short BorderBottom, short BorderRight,
            short FillPattern, short FillForegroundColor, Font font) {
        this.Alignment = Alignment;
        this.BorderTop = BorderTop;
        this.BorderLeft = BorderLeft;
        this.BorderBottom = BorderBottom;
        this.BorderRight = BorderRight;
        this.FillPattern = FillPattern;
        this.FillForegroundColor = FillForegroundColor;
        this.font = font;
    }

    public short getAlignment() {
        return Alignment;
    }

    public void setAlignment(short Alignment) {
        this.Alignment = Alignment;
    }

    public short getBorderBottom() {
        return BorderBottom;
    }

    public void setBorderBottom(short BorderBottom) {
        this.BorderBottom = BorderBottom;
    }

    public short getBorderLeft() {
        return BorderLeft;
    }

    public void setBorderLeft(short BorderLeft) {
        this.BorderLeft = BorderLeft;
    }

    public short getBorderRight() {
        return BorderRight;
    }

    public void setBorderRight(short BorderRight) {
        this.BorderRight = BorderRight;
    }

    public short getBorderTop() {
        return BorderTop;
    }

    public void setBorderTop(short BorderTop) {
        this.BorderTop = BorderTop;
    }

    public short getFillForegroundColor() {
        return FillForegroundColor;
    }

    public void setFillForegroundColor(short FillForegroundColor) {
        this.FillForegroundColor = FillForegroundColor;
    }

    public short getFillPattern() {
        return FillPattern;
    }

    public void setFillPattern(short FillPattern) {
        this.FillPattern = FillPattern;
    }

    public Font getFont() {
        return font;
    }

    public void setFont(Font font) {
        this.font = font;
    }
}
// </editor-fold>

